{"cells":[{"metadata":{},"cell_type":"markdown","source":"# DS 3 Correction - Exercice 3 - SQL"},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE musiciens (\n id_mus INT PRIMARY KEY,\n Nom TEXT,\n Prenom TEXT,\n Mail TEXT,\n Anciennete INT,\n Code_instrument INT\n \n);\n\nINSERT INTO musiciens (id_mus, Nom, Prenom, Mail, Anciennete, Code_instrument) VALUES\n(1, 'Dupont', 'Claudine', 'cDupont@mail.fr', 5, 1),\n(2, 'Leclerc', 'Jean', 'leclerc.jean@mail.fr', 2, 2),\n(3, \"M'bake\", 'Doume', 'doubake@mail.fr', 7, 5),\n(4, 'Descarte', 'Hugo', 'Hugo.arnaque@mail.fr', 4, 1),\n(5, 'Tourelle', 'Charlène', 'char.tour@mail.fr', 1, 7),\n(6, 'Dupuis', 'Alice', 'dupuis.Alice@mail.fr', 9, 4),\n(7, 'Tourelle', 'Phillipe', 'filoutou@mail.fr', 1, 4),\n(8, 'Fernandez', 'Kader', 'kader454@mail.fr', 4, 1),\n(9, 'Dujardin', 'Laure', 'dujardin.laure@mail.fr', 1, 6),\n(10, 'Lefevre', 'Marie', 'lefevre.marie@mail.fr', 2, 2);\n\n\nCREATE TABLE instruments (\n Code_instrument INT PRIMARY KEY,\n type_ins TEXT\n);\n\nINSERT INTO instruments (Code_instrument, type_ins)\nVALUES \n(1, 'Violon'), \n(2, 'Violon'), \n(3, 'Alto'), \n(4, 'Alto'), \n(5, 'Violoncelle'), \n(6, 'Contrebasse'), \n(7, 'Harpe');\n\n\n\nCREATE TABLE Admin (\n Poste TEXT PRIMARY KEY,\n id_mus INT,\n Anciennete_poste INT\n);\n\nINSERT INTO Admin (Poste, id_mus, Anciennete_poste) \nVALUES \n('Archiviste', 8, 6),\n(\"Chef d'Orchestre\", 4, 1),\n('Président', 1, 7);","execution_count":1,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM instruments;","execution_count":2,"outputs":[{"output_type":"execute_result","execution_count":2,"data":{"text/plain":"Code_instrument\ttype_ins\n1\tViolon\n2\tViolon\n3\tAlto\n4\tAlto\n5\tVioloncelle\n6\tContrebasse\n7\tHarpe","text/html":"
Code_instrumenttype_ins
1Violon
2Violon
3Alto
4Alto
5Violoncelle
6Contrebasse
7Harpe
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM musiciens;","execution_count":3,"outputs":[{"output_type":"execute_result","execution_count":3,"data":{"text/plain":"id_mus\tNom\tPrenom\tMail\tAnciennete\tCode_instrument\n1\tDupont\tClaudine\tcDupont@mail.fr\t5\t1\n2\tLeclerc\tJean\tleclerc.jean@mail.fr\t2\t2\n3\tM'bake\tDoume\tdoubake@mail.fr\t7\t5\n4\tDescarte\tHugo\tHugo.arnaque@mail.fr\t4\t1\n5\tTourelle\tCharlène\tchar.tour@mail.fr\t1\t7\n6\tDupuis\tAlice\tdupuis.Alice@mail.fr\t9\t4\n7\tTourelle\tPhillipe\tfiloutou@mail.fr\t1\t4\n8\tFernandez\tKader\tkader454@mail.fr\t4\t1\n9\tDujardin\tLaure\tdujardin.laure@mail.fr\t1\t6\n10\tLefevre\tMarie\tlefevre.marie@mail.fr\t2\t2","text/html":"
id_musNomPrenomMailAncienneteCode_instrument
1DupontClaudinecDupont@mail.fr51
2LeclercJeanleclerc.jean@mail.fr22
3M'bakeDoumedoubake@mail.fr75
4DescarteHugoHugo.arnaque@mail.fr41
5TourelleCharlènechar.tour@mail.fr17
6DupuisAlicedupuis.Alice@mail.fr94
7TourellePhillipefiloutou@mail.fr14
8FernandezKaderkader454@mail.fr41
9DujardinLauredujardin.laure@mail.fr16
10LefevreMarielefevre.marie@mail.fr22
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM Admin;","execution_count":4,"outputs":[{"output_type":"execute_result","execution_count":4,"data":{"text/plain":"Poste\tid_mus\tAnciennete_poste\nArchiviste\t8\t6\nChef d'Orchestre\t4\t1\nPrésident\t1\t7","text/html":"
Posteid_musAnciennete_poste
Archiviste86
Chef d'Orchestre41
Président17
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT Nom, Prenom \nFROM musiciens \nWHERE Code_instrument = 4;","execution_count":5,"outputs":[{"output_type":"execute_result","execution_count":5,"data":{"text/plain":"Nom\tPrenom\nDupuis\tAlice\nTourelle\tPhillipe","text/html":"
NomPrenom
DupuisAlice
TourellePhillipe
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT Nom, Prenom \nFROM musiciens \nWHERE Anciennete >= 5;","execution_count":6,"outputs":[{"output_type":"execute_result","execution_count":6,"data":{"text/plain":"Nom\tPrenom\nDupont\tClaudine\nM'bake\tDoume\nDupuis\tAlice","text/html":"
NomPrenom
DupontClaudine
M'bakeDoume
DupuisAlice
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"UPDATE musiciens \nSET Mail = 'tour.char@mail.fr' \nWHERE id_mus = 5;","execution_count":7,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM musiciens;","execution_count":8,"outputs":[{"output_type":"execute_result","execution_count":8,"data":{"text/plain":"id_mus\tNom\tPrenom\tMail\tAnciennete\tCode_instrument\n1\tDupont\tClaudine\tcDupont@mail.fr\t5\t1\n2\tLeclerc\tJean\tleclerc.jean@mail.fr\t2\t2\n3\tM'bake\tDoume\tdoubake@mail.fr\t7\t5\n4\tDescarte\tHugo\tHugo.arnaque@mail.fr\t4\t1\n5\tTourelle\tCharlène\ttour.char@mail.fr\t1\t7\n6\tDupuis\tAlice\tdupuis.Alice@mail.fr\t9\t4\n7\tTourelle\tPhillipe\tfiloutou@mail.fr\t1\t4\n8\tFernandez\tKader\tkader454@mail.fr\t4\t1\n9\tDujardin\tLaure\tdujardin.laure@mail.fr\t1\t6\n10\tLefevre\tMarie\tlefevre.marie@mail.fr\t2\t2","text/html":"
id_musNomPrenomMailAncienneteCode_instrument
1DupontClaudinecDupont@mail.fr51
2LeclercJeanleclerc.jean@mail.fr22
3M'bakeDoumedoubake@mail.fr75
4DescarteHugoHugo.arnaque@mail.fr41
5TourelleCharlènetour.char@mail.fr17
6DupuisAlicedupuis.Alice@mail.fr94
7TourellePhillipefiloutou@mail.fr14
8FernandezKaderkader454@mail.fr41
9DujardinLauredujardin.laure@mail.fr16
10LefevreMarielefevre.marie@mail.fr22
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT Nom, Prenom\nFROM musiciens\nJOIN instruments ON musiciens.Code_instrument = instruments.Code_instrument\nWHERE type_ins = 'Violon'","execution_count":9,"outputs":[{"output_type":"execute_result","execution_count":9,"data":{"text/plain":"Nom\tPrenom\nDupont\tClaudine\nLeclerc\tJean\nDescarte\tHugo\nFernandez\tKader\nLefevre\tMarie","text/html":"
NomPrenom
DupontClaudine
LeclercJean
DescarteHugo
FernandezKader
LefevreMarie
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Admin\nVALUES\n('Trésorier', 2, 0)","execution_count":11,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM Admin;","execution_count":12,"outputs":[{"output_type":"execute_result","execution_count":12,"data":{"text/plain":"Poste\tid_mus\tAnciennete_poste\nArchiviste\t8\t6\nChef d'Orchestre\t4\t1\nPrésident\t1\t7\nTrésorier\t2\t0","text/html":"
Posteid_musAnciennete_poste
Archiviste86
Chef d'Orchestre41
Président17
Trésorier20
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT type_ins\nFROM musiciens\nJOIN instruments ON musiciens.Code_instrument = instruments.Code_instrument\nJOIN Admin ON musiciens.id_mus = Admin.id_mus\nWHERE Poste = 'Président'","execution_count":13,"outputs":[{"output_type":"execute_result","execution_count":13,"data":{"text/plain":"type_ins\nViolon","text/html":"
type_ins
Violon
"},"metadata":{}}]}],"metadata":{"kernelspec":{"name":"sql","display_name":"SQL","language":"sql"}},"nbformat":4,"nbformat_minor":2}